package persistence.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import persistence.DbUtils;
import persistence.TransactionManager;
import persistence.dto.ProductDTO;
import persistence.dto.ProductGroupDTO;
import persistence.dto.StorageUnitDTO;

public class ContainerToProductsDBDAO implements IContainerToProductsDAO
{

	private static ContainerToProductsDBDAO instance;
	
	public static ContainerToProductsDBDAO getInstance()
	{
		if(instance == null)
		{
			instance = new ContainerToProductsDBDAO();
		}
		return instance;
	}
	
	private ContainerToProductsDBDAO()
	{
		
	}
	
	@Override 
	public List<Integer> getAllContainers(int prodId)
	{
		List<Integer> containerIDs = new ArrayList<Integer>();
		Connection connection = TransactionManager.getConnection();
		String sql = "select containerID from ContainerToProducts where productID = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, prodId);
			
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				containerIDs.add(rs.getInt(1));
			}
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DbUtils.safeClose(stmt);
			DbUtils.safeClose(rs);
		}
		return containerIDs;
	}

	@Override
	public List<Integer> getAllProducts(int pcID)
	{
		List<Integer> productIDs = new ArrayList<Integer>();
		Connection connection = TransactionManager.getConnection();
		String sql = "select productID from ContainerToProducts where containerID = ?";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, pcID);
			
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				productIDs.add(rs.getInt(1));
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			DbUtils.safeClose(stmt);
			DbUtils.safeClose(rs);
		}
		
		return productIDs;
	}

	@Override
	public int add(ProductDTO prodDTO, ProductGroupDTO pgDTO)
	{
		return add(prodDTO, pgDTO.getId());
	}

	@Override
	public int add(ProductDTO prodDTO, StorageUnitDTO suDTO)
	{
		return add(prodDTO, suDTO.getId());
	}
	
	private int add(ProductDTO prodDTO, int pcID)
	{
		Connection connection = TransactionManager.getConnection();
		// first check if this is already in there
		String sql1 = "select* from ContainerToProducts where containerID = ? and productID = ?";
		PreparedStatement stmt1 = null;
		ResultSet rs1 = null;
		
		String sql = "insert into ContainerToProducts (containerID, productID) values(?,?)";
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			stmt1 = connection.prepareStatement(sql1);
			stmt1.setInt(1, pcID);
			stmt1.setInt(2, prodDTO.getId());
			rs1 = stmt1.executeQuery();
			
			if(rs1.next())
			{
				// already in it
				return 0;
			}
			
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, pcID);
			stmt.setInt(2, prodDTO.getId());
			
			 if(stmt.executeUpdate() == 1)
             {
                 return 1;
             }
			 else
				 throw new SQLException();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			DbUtils.safeClose(stmt1);
			DbUtils.safeClose(rs1);
			DbUtils.safeClose(stmt);
			DbUtils.safeClose(rs);
		}
		
		return 1;
	}

	@Override
	public boolean edit(ProductDTO prodDTO, ProductGroupDTO pgDTO)
	{
            //grab id
		return false;
	}

	@Override
	public boolean edit(ProductDTO prodDTO, StorageUnitDTO suDTO)
	{
            //grab id
	
		return false;
	}

	@Override
	public boolean remove(ProductDTO prodDTO, ProductGroupDTO pgDTO)
	{
            PreparedStatement stmt = null;
            
            try {
                    Connection connection = TransactionManager.getConnection();
                    String sql = "delete from ContainerToProducts where"+
                    " containerID = ? and productID = ?";
                    stmt = TransactionManager.getConnection().prepareStatement(sql);

                    stmt.setInt(1, pgDTO.getId());
                    stmt.setInt(2, prodDTO.getId());

                    if(stmt.executeUpdate() == 1)
                    {
                        return true;
                    }
                   else
                    {
                        throw new SQLException();
                    }
		} 
            catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			DbUtils.safeClose(stmt);
		}
		
		return false;


	}

	@Override
	public boolean remove(ProductDTO prodDTO, StorageUnitDTO suDTO)
	{
            PreparedStatement stmt = null;
            
            try {
                Connection connection = TransactionManager.getConnection();
                String sql = "delete from ContainerToProducts where"+
                " containerID = ? and productID = ?";
                stmt = TransactionManager.getConnection().prepareStatement(sql);
                
                stmt.setInt(1, suDTO.getId());
                stmt.setInt(2, prodDTO.getId());
                
                if(stmt.executeUpdate() == 1)
                {
                    return true;
                }
               else
				 throw new SQLException();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			DbUtils.safeClose(stmt);
		}
		
		return false;
	}

	@Override
	public boolean remove(StorageUnitDTO suDTO)
	{
		PreparedStatement stmt = null;
        
        try {
            String sql = "delete from ContainerToProducts where containerID = ?";
            stmt = TransactionManager.getConnection().prepareStatement(sql);
            
            stmt.setInt(1, suDTO.getId());
            
            if(stmt.executeUpdate() == 1)
            {
                return true;
            }
           else
        	   return false;
			// throw new SQLException();
	} catch (SQLException e)
	{
		e.printStackTrace();
	}
	finally
	{
		DbUtils.safeClose(stmt);
	}
		return false;
	}

	@Override
	public boolean remove(ProductGroupDTO pgDTO)
	{
		PreparedStatement stmt = null;

		try
		{
			String sql = "delete ContainerToProducts where containerID = ?";
			stmt = TransactionManager.getConnection().prepareStatement(sql);

			stmt.setInt(1, pgDTO.getId());

			if (stmt.executeUpdate() == 1)
			{
				return true;
			} else
				return false;
				//throw new SQLException();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			DbUtils.safeClose(stmt);
		}
		return false;

	}
}
